//
//  SeaarchQueue.swift
//  MySwiftObject
//
//  Created by wangws1990 on 2020/11/26.
//  Copyright © 2020 wangws1990. All rights reserved.
//  

import UIKit
import SQLite.Swift

private let SearchTable    = "SearchTable"

private let id             = Expression<Int>("primaryId")
private let hotName        = Expression<String?>("hotName")
private let updateTime     = Expression<TimeInterval>("time")
private let createTime     = Expression<TimeInterval>("createTime")

class BaseSearchQueue{
    fileprivate lazy var table: Table? = {
        guard let db = dataBase.db else { return nil }
        let tb = Table(SearchTable)
        guard let _ = try? db.run(tb.create(ifNotExists: true, block: { (table) in
            //注意.autoincrement
            table.column(id,primaryKey: .autoincrement)
            table.column(hotName)
            table.column(updateTime)
            
        })) else { return nil }
        return tb
    }()
}
extension BaseSearchQueue{
     public func insertData(keyword :String,completion:@escaping ((_ success : Bool) -> ())){
        guard let db = dataBase.db else { return completion(false) }
        guard let tb = self.table else { return completion(false) }
        let count = haveData(keyword)
        if !count{
            let time : TimeInterval = NSDate ().timeIntervalSince1970
            let insertdata = tb.insert(hotName <- keyword,updateTime <- time,createTime <- time)
            guard let _ = try? db.run(insertdata) else { return completion(false) }
            completion(true)
        }else{
            updateData(keyword: keyword, completion: completion)
        }
    }
    public func updateData(keyword :String,completion:@escaping ((_ success : Bool) -> ())){
        guard let db = dataBase.db else { return completion(false) }
        guard let tb = self.table else { return completion(false) }
        let count = haveData(keyword)
        if !count {
            insertData( keyword: keyword, completion: completion)
        }else{
            let table = tb.filter(hotName == keyword)
            let time  = NSDate ().timeIntervalSince1970
            let update = table.update(hotName <- keyword,updateTime <- time)
            guard let _ = try? db.run(update) else { return completion(false) }
            completion(true)
        }
    }
    public func deleteData(keyword :String,completion:@escaping ((_ success : Bool) -> ())){
        guard let db = dataBase.db else { return completion(false) }
        guard let tb = self.table else { return completion(false) }
        let alice = tb.filter(hotName == keyword)
        guard let _ = try? db.run(alice.delete()) else { return completion(false) }
        completion(true)
    }
    public func deleteData(keywords :[String],completion:@escaping ((_ success : Bool) -> ())){
        guard let db = dataBase.db else { return completion(false) }
        guard let tb = self.table else { return completion(false) }
        do {
            try db.transaction {
                keywords.forEach { (title) in
                    let alice = tb.filter(hotName == title)
                    guard let _ = try? db.run(alice.delete()) else { return }
                }
            }
            completion(true)
        } catch  {
            completion(false)
        }
    }
    public func searchData(page : Int,size : Int = 20,completion:@escaping ((_ datas : [String]) ->())){
        guard let db = dataBase.db else { return completion([]) }
        guard let tb = self.table else { return completion([]) }
        let order = tb.order(updateTime.desc).limit(size, offset: (page - 1) * size)
        guard let datas : AnySequence<Row> = try? db.prepare(order) else {
            return completion([])
        }
        decodeData(listData: datas,completion: completion)
    }
    public func searchData(completion:@escaping ((_ datas : [String]) ->Void)){
        guard let db = dataBase.db else { return completion([]) }
        guard let tb = self.table else { return completion([]) }
        guard let datas : AnySequence<Row> = try? db.prepare(tb) else {
            return completion([])
        }
        decodeData(listData: datas,completion: completion)
    }
    private func decodeData(listData : AnySequence<Row>,completion:@escaping ((_ datas : [String]) ->Void)){
        var contentData : [String] = []
        listData.forEach { (objc) in
            if let json = try? objc.get(hotName) {
                contentData.append(json)
            }
        }
        completion(contentData)
    }
    //
    fileprivate func columns(table :String = SearchTable,column :String) -> Bool{
        guard let db = dataBase.db else { return false }
        guard let s = try? db.prepare("PRAGMA table_info(" + table + ")" ) else { return false }
        var columnDatas :[String] = []
        for row in s {
            if let str = row[1] as? String{
                columnDatas.append(str)
            }
        }
        let list = columnDatas.filter { (item) -> Bool in
            return item == column
        }
        return list.count > 0
    }
    fileprivate func haveData(_ keyword :String) ->Bool{
        guard let db = dataBase.db else { return false }
        guard let tb = self.table else { return false }
        let haveColumn = columns(column: "createTime")
        if !haveColumn {//表结构升级 添加一列createTime
            let add = tb.addColumn(createTime,defaultValue:0)
            guard let _ = try? db.run(add) else { return false }
        }
        let alice = tb.filter(hotName == keyword)
        guard let count = try? db.scalar(alice.count) else { return false }
        return count > 0 ? true : false
    }
}
